#!/usr/bin/env python
#-*- coding: utf-8 -*-

import os
import types
import time
from threading import Lock
import kinterbasdb

try:
    if kinterbasdb.FB_API_VER == 20:
        kinterbasdb.init(type_conv = 200)  ## Firebird 2.0.x
except: 
    pass

_CHARSET = 'UTF8'
_role='MORE_POWERFUL_ROLE'


class BirdCon(object):
    def __init__(self, dbfile, host='localhost', user='sysdba', 
                    password='masterkey',
                    charset=_CHARSET):
        self.host = host
        self.dbfile = dbfile
        self.user = user
        self.password = password
        self.charset = charset
    
    def preconnect(self):
        try:
            return kinterbasdb.connect(host = self.host, database = self.dbfile,
                                user = self.user, password = self.password, 
                                charset = self.charset, role = _role)
        except Exception, e:
            raise Exception('connect FireBird Error: %s' % str(e))
    
    def creatdb(self):
        dirs = None
        if 'localhost' == self.host:
            dirs = os.path.dirname(self.dbfile)
        else:
            dirs = os.path.dirname('\\\\%s%s' % (self.host, self.dbfile))
        if dirs is not None:
            try:
                if not os.path.exists(dirs):
                    os.makedirs(dirs)
            except Exception, e:
                raise Exception("makedirs Error: %s" % str(e))
        Create = "create database '%s' user '%s' password '%s'" % (self.dbfile, 
                self.user, self.password)
        kinterbasdb.create_database(Create)
    
    
class FireBird(BirdCon):
    def __init__(self, dbfile, host='', user='sysdba', 
                    password='masterkey',
                    charset='UTF8'):
        BirdCon.__init__(self, dbfile, host, user, password, charset)
        self._DB = None
        self._lock = Lock()
    
    def connect(self):
        if self._DB is not None:
            return (True, '')
        else:
            try:
                self._DB = self.preconnect()
                return (True, 'Connect FireBird %s OK.' % self.dbfile)
            except Exception, e:
                return (False, str(e))
    
    
    def connected(self):
        return self._DB is not None
    
    def disconnect(self):
        if self._DB:
            try:
                self._DB.close()
            except:pass
        self._DB = None
    
    def reconnect(self):
        self.disconnect()
        return self.connect()
        
    def execute(self, sql, param=None):
        if not self.connected():
            self._lock.acquire()
            result, ret = self.reconnect()
            self._lock.release()
            if not result:
                time.sleep(0.1)
                return (result, ret)
        if self.connected():
            self._lock.acquire()
            try:
                cur = None
                cur = self._DB.cursor()
                if param is not None:
                    if type(param) not in (types.ListType, types.TupleType, types.DictionaryType):
                        param = tuple(param)
                    ret = cur.execute(sql, param)
                else:
                    ret = cur.execute(sql)
                    print ret
                try:
                    ret = cur.fetchall()
                except:
                    ret = cur.rowcount
                result = True
                cur.close()
                self._DB.commit()
            except Exception, args:
                try:
                    self._DB.rollback()
                    if cur:
                        cur.close()
                except Exception:
                    self.reconnect()
                ret = 'Execute error.(%s)' % str(args)
                result = False
            self._lock.release()
        else:
            result = False
            ret = 'Execute error.(Connection is not found)'
        return (result, ret)
        
    def executemany(self, sqllist):
        if not self.connected():
            self._lock.acquire()
            result, ret = self.reconnect()
            self._lock.release()
            if not result:
                time.sleep(0.1)
                return (result, ret)
        if self.connected():
            self._lock.acquire()
            try:
                cur = None
                cur = self._DB.cursor()
                rlist = []
                for sql in sqllist:
                    try:
                        if type(sql) in (types.ListType, types.TupleType):
                            param = sql[1]
                            sql = sql[0]
                        else:
                            param = None
                        if param is None:
                            ret = cur.execute(sql)
                        else:
                            if type(param) in (types.ListType, types.TupleType, types.DictionaryType):
                                ret = cur.execute(sql, param)
                            else:
                                param = tuple(param)
                                ret = cur.execute(sql, param)
                        try:
                            ret = cur.fetchall()
                        except:
                            ret = cur.rowcount
                        result = True
                    except Exception, args:
                        ret = 'Execute error.(%s)' % str(args)
                        result = False
                    rlist.append((result, ret))
                cur.close()
                self._DB.commit()
                result = True
                ret = rlist
            except Exception, args:
                try:
                    self._DB.rollback()
                    if cur:
                        cur.close()
                except Exception:
                    self.reconnect()
                ret = 'Executemany error.(%s)' % str(args)
                result = False
            self._lock.release()
        else:
            result = False
            ret = 'Executemany error.(Connection is not found)'
        return (result, ret)

    
class BirdGroup(BirdCon):
    pass

def test():
    T = FireBird(r"r:\FT.fdb")
    print T.connect()
    print T.execute("insert into thread(tid, m1, m2) values(10001, 'aaaaa','bbbb')")
#    print T.execute("select * from thread where  tid = 78")
#    print T.execute("recreate table thread (tid int, m1 varchar(32), m2 varchar(32))")
#    sqls = []
#    import uuid
#    t = time.time()
#    for i in xrange(101, 10001):
#        s1 = uuid.uuid4().hex
#        s2 = uuid.uuid4().hex
#        sqls.append(('insert into thread(tid, m1, m2) values(?, ?, ?)', (i, s1, s2)))
#    print T.executemany(sqls)
#    print time.time() - t 
#    T.disconnect()
    

if __name__ == '__main__':
    test()
    